package cn.com.fssg.platform.base.handler.impl.cl.tongji;

import cn.com.fssg.platform.base.handler.InterfaceHandler;
import cn.com.fssg.platform.biz.BizUtil;
import com.alibaba.fastjson.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class ClHomePageCounterInterfaceHandler implements InterfaceHandler {
//    private NamedParameterJdbcTemplate jdbcTemplate = BizUtil.cur().tenantJdbcTemplate("CJRLHHConnString");
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;


    @Override
    public Object run(HttpServletRequest request, JSONObject jsonObject) throws Exception {
        String xlSql = "select count(1) from cl_apply_xunlian";
        String xlJiesuanSql = "select count(1) from cl_apply_xunlian_org_jiesuan";
        String fujuSql = "select count(1) from cl_apply_fuju";
        String fujuJiesuanSql = "select count(1) from cl_apply_fuju_org_jiesuan where finish = '1' ";
        String orgCountSql = "select count(1) from cl_org ";
        String cjrCountSql = "SELECT COUNT(DISTINCT(cjr_name)) AS total" +
                " FROM (" +
                "    SELECT cjr_name FROM cl_apply_xunlian" +
                "    UNION" +
                "    SELECT cjr_name FROM cl_apply_fuju" +
                " ) AS names";
        String xlHasCjrCardSql = getCountSql("cl_apply_xunlian");
        String fjHasCjrCardSql = getCountSql("cl_apply_fuju");
        Map<String, Object> paraMap = new HashMap<>();
        Map<String, Object> resultMap = new HashMap<>();
        Map<String, Object> queryMap;

        try {
            queryMap = jdbcTemplate.queryForMap(xlSql, paraMap);
            resultMap.put("xunlian", queryMap.get("count"));
            queryMap = jdbcTemplate.queryForMap(xlJiesuanSql, paraMap);
            resultMap.put("xunlianJiesuan", queryMap.get("count"));
            queryMap = jdbcTemplate.queryForMap(fujuSql, paraMap);
            resultMap.put("fuju", queryMap.get("count"));
            queryMap = jdbcTemplate.queryForMap(fujuJiesuanSql, paraMap);
            resultMap.put("fujuJiesuan", queryMap.get("count"));
            queryMap = jdbcTemplate.queryForMap(orgCountSql, paraMap);
            resultMap.put("orgCount", queryMap.get("count"));
            queryMap = jdbcTemplate.queryForMap(cjrCountSql, paraMap);
            resultMap.put("xcjrCount", queryMap.get("total"));
            List<Map<String, Object>> list = jdbcTemplate.queryForList(xlHasCjrCardSql, paraMap);
            list = putArea(list);
            List<Map<String, Object>> list1 = jdbcTemplate.queryForList(fjHasCjrCardSql, paraMap);
            list1 = putArea(list1);
            Map<String, Object> hasCjrCardMap = new HashMap<>();
            hasCjrCardMap.put("fuju",list1);
            hasCjrCardMap.put("xunlian",list);
            resultMap.put("hasCardCount", hasCjrCardMap);
            String clApplyXunlian = getHasCjrCardPassSql("cl_apply_xunlian");
            List<Map<String, Object>> xlPassList = jdbcTemplate.queryForList(clApplyXunlian, paraMap);
            xlPassList = putArea(xlPassList);
            String clApplyFuju = getHasCjrCardPassSql("cl_apply_fuju");
            List<Map<String, Object>> fjPassList = jdbcTemplate.queryForList(clApplyFuju, paraMap);
            fjPassList = putArea(fjPassList);
            Map<String, Object> hasCardPassMap = new HashMap<>();
            hasCardPassMap.put("fuju",fjPassList);
            hasCardPassMap.put("xunlian",xlPassList);
            resultMap.put("hasCardPassCount", hasCardPassMap);
        } catch (Exception e){
            return "jdbcTemplate:" + jdbcTemplate;
        }
        return resultMap;
    }

    private List<Map<String, Object>> putArea(List<Map<String, Object>> list){
        for (Map<String, Object> map : list) {
            String areaName = (String) map.get("area_name");
            switch (areaName){
                case "440605004000":
                    map.put("area_name", "南海区");
                    break;
                case "440604001000":
                    map.put("area_name", "禅城区");
                    break;
                case "440606005000":
                    map.put("area_name", "顺德区");
                    break;
                case "440607001000":
                    map.put("area_name", "三水区");
                    break;
                case "440608001000":
                    map.put("area_name", "高明区");
                    break;
                default:
                    break;
            }
        }
        return list;
    }

    private String getCountSql(String tableName){
        String sql = "WITH regions AS (" +
                "SELECT unnest(ARRAY['440605004000', '440604001000', '440606005000', '440607001000', '440608001000']) AS region " +
                ") " +
                " SELECT " +
                " r.region AS area_name, " +
                " SUM(CASE WHEN a.has_cjr_card = 1 THEN 1 ELSE 0 END) AS has_cjr_card, " +
                " SUM(CASE WHEN a.has_cjr_card = 0 THEN 1 ELSE 0 END) AS has_no_card, " +
                " COUNT(a.has_cjr_card) AS total" +
                " FROM regions r " +
                " LEFT JOIN %s a ON r.region = a.cjr_hukou_qu " +
                " AND a.submit_time >= DATE_TRUNC('year', CURRENT_DATE) " +
                " GROUP BY r.region " +
                " ORDER BY r.region";
        return String.format(sql, tableName);
    }

    private String getHasCjrCardPassSql(String tableName){
        String sql = "WITH regions AS (" +
                "SELECT unnest(ARRAY['440605004000', '440604001000', '440606005000', '440607001000', '440608001000']) AS region " +
                ") " +
                " SELECT " +
                " r.region AS area_name, " +
                " SUM(CASE WHEN a.has_cjr_card = 1 THEN 1 ELSE 0 END) AS has_cjr_card, " +
                " SUM(CASE WHEN a.has_cjr_card = 0 THEN 1 ELSE 0 END) AS has_no_card, " +
                " COUNT(a.has_cjr_card) AS total, " +
                " CASE WHEN COUNT(a.has_cjr_card) > 0 THEN " +
                "   ROUND(SUM(CASE WHEN a.finish = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(a.has_cjr_card), 2) " +
                " ELSE 0 END AS total_pass_rate, " +
                " CASE WHEN SUM(CASE WHEN a.has_cjr_card = 1 THEN 1 ELSE 0 END) > 0 THEN " +
                "   ROUND(SUM(CASE WHEN a.has_cjr_card = 1 AND a.finish = 1 THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN a.has_cjr_card = 1 THEN 1 ELSE 0 END), 2) " +
                " ELSE 0 END AS has_card_pass_rate, " +
                " CASE WHEN SUM(CASE WHEN a.has_cjr_card = 0 THEN 1 ELSE 0 END) > 0 THEN " +
                "   ROUND(SUM(CASE WHEN a.has_cjr_card = 0 AND a.finish = 1 THEN 1 ELSE 0 END) * 100.0 / SUM(CASE WHEN a.has_cjr_card = 0 THEN 1 ELSE 0 END), 2) " +
                " ELSE 0 END AS no_card_pass_rate " +
                " FROM regions r " +
                " LEFT JOIN %s a ON r.region = a.cjr_hukou_qu " +
                " AND a.submit_time >= DATE_TRUNC('year', CURRENT_DATE) " +
                " GROUP BY r.region " +
                " ORDER BY r.region";
        return String.format(sql, tableName);
    }
}
